<?php
@ob_start();
@set_time_limit(0);

header("Content-type:text/html;charset=utf-8");
date_default_timezone_set('PRC');

// 加载框架引导文件
require_once '../thinkphp/base.php';

include_once 'library/phpexcel/PHPExcel.php';
/** PHPExcel_IOFactory */
include_once 'library/phpexcel/PHPExcel/IOFactory.php';

$config = include '../application/database.php';

$db = \think\Db::connect([
    'type'     => "{$config['type']}",
    'hostname' => "{$config['hostname']}",
    'hostport' => "{$config['hostport']}",
    'database' => "{$config['database']}",
    'username' => "{$config['username']}",
    'password' => "{$config['password']}",
    'prefix'   => "{$config['prefix']}",
]);


// $staffInfo = $db->name('staff')->where(['name'=>'张三', 'idcard'=>'370682199211142730'])->find();
// var_dump($staffInfo);
// die;

// 验证后台登录
$data_config = include '../application/config.php';
$token_key = $data_config['token']['key'];

$admin = \think\Session::get('admin');
if (empty($admin)) {
    echo errormsg("未登录或登录过期");
    exit();
}

$my = $db->name('admin')->where(['id'=>$admin['id'], 'status'=>'normal'])->find();
if (empty($admin)) {
    echo errormsg("未登录或登录过期.");
    exit();
}

//校验安全码，可用于判断关键信息发生了变更需要重新登录
if (!isset($admin['safecode']) || getEncryptSafecode($my, $token_key) !== $admin['safecode']) {
    echo errormsg("未登录或登录过期！");
    exit();
}

// 处理导入
$table_loadtable = 'salary';//表格具体单元格明细表

$year = getp('year');
$month = getp('month');
$filename = getp('filename');

if (empty($year)) {
    echo errormsg("请选择年份");
    exit();
}

if (empty($month)) {
    echo errormsg("请选择月份");
    exit();
}

if (empty($filename)) {
    echo errormsg("请选择文件");
    exit();
}

$filePath = $filename;
$ntime = time();
if(!file_exists($filePath))
{
    echo errormsg("文件不存在！");
    exit();
}

/**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/
$PHPReader = new PHPExcel_Reader_Excel2007();
//只读区数据，不读区格式
$PHPReader->setReadDataOnly(true);
if(!$PHPReader->canRead($filePath)){
    $PHPReader = new PHPExcel_Reader_Excel5();
    if(!$PHPReader->canRead($filePath)){
        echo errormsg("EXCEL无法读取！");
        exit();
    }
}

$PHPExcel = $PHPReader->load($filePath); // 载入excel文件
$sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumm = $sheet->getHighestColumn(); // 取得总列数
$end_index = PHPExcel_Cell::columnIndexFromString($highestColumm);//由列名转为列数('AB'->28)

$dalei_arr = array();
$dalei_tmp = '';
/** 循环读取每个单元格的数据 */
for ($row = 1; $row <= $highestRow; $row++){//行数是以第1行开始

    $temp = array();
    for ($column = 0; $column <= $end_index; $column++) {//列数是以A列开始
        $col_name = PHPExcel_Cell::stringFromColumnIndex($column);//由列数反转列名(0->'A')
        $temp[] = $sheet->getCell($col_name.$row)->getValue();

        if ($row == 1) {
            $now_value = $sheet->getCell($col_name.$row)->getValue();

            if (!empty($now_value)) {
                $dalei_tmp = $now_value;
                $dalei_arr[$column] = $now_value;
            }
            // else {
            //     $dalei_arr[$column] = $dalei_tmp;
            // }
        }
    }

    if ($row>=2) {
        if (!empty($temp[0])) {//第一个单元格为渠道编号并且渠道编号不为空
            if (!writetable($dalei_arr,$temp,$year,$month)) {
                echo errormsg("导入错误，异常终止！");
                exit();
            }
        }

    }
}

echo success("导入完成");



function writetable($dalei_arr,$data,$year,$month)
{
    global $db;

    $html = array();
    foreach ($dalei_arr as $key => $value) {

        if ($key >= 4) {
            $tmp = array(
                'key'=>$key,
                'name'=>$value,
                'value'=>$data[$key],
            );

            $html[] = $tmp;
        }
    }

    $html = serialize($html);

    $name=$data[0];
    $idcard=$data[1];
    $mobile=$data[2];
    $company_name=$data[3];

    $datafield = array(
        'name'=>$name,
        'idcard'=>$idcard,
        'mobile'=>$mobile,
        'company_name'=>$company_name,
        'other'=>$html,
        'year'=>$year,
        'month'=>$month,
    );

    $exist = $db->name('salary')->where(['name'=>$name, 'idcard'=>$idcard, 'mobile'=>$mobile, 'company_name'=>$company_name])->find();
    $time = time();
    if (empty($exist)) {
        $datafield['createtime'] = $time;
        $datafield['updatetime'] = $time;
        $res = $db->name('salary')->insert($datafield);
    } else {
        $datafield['updatetime'] = $time;
        $res = $db->name('salary')->where('id', $exist['id'])->update($datafield);
    }

    return true;

    // if(!$res)
    // {
    //     echo errormsg("写入失败！");
    //     return false;
    // }
    // else{
    //     return true;
    // }

}


function errormsg($info=""){
        $msg['info'] = $info;
        $msg['status'] = "0";
        return json_encode($msg);
}
function success($info){
        $msg['info'] = $info;
        $msg['status'] = "1";
        return json_encode($msg);
}

function getp($name, $default = ''){
    $value = isset($_POST[$name]) ? $_POST[$name] : $default;
    $value = filter($value);
    return $value;
}

/**
 * 使用反斜线引用数据 防注入 前端xss过滤 (使用pdo, 省略addslashes过滤)
 */
function filter($string, $force = 1, $allow='') {
    if($force) {
        if(is_array($string)) {
            foreach ($string as $key => $val) {
                $string[$key] = filter($val, $force, $allow);
            }
        } else {
            $string = remove_xss($string, $allow);
            $string = addslashes($string);
        }
    } return $string;
}

/**
 * 移除HTML中的危险代码，如iframe和script
 * @param $val
 * @return unknown_type
 */
function remove_xss($content,$allow='') {
    $danger = 'javascript,vbscript,expression,applet,meta,xml,blink,link,style,script,embed,object,iframe,frame,frameset,ilayer,layer,bgsound,title,base,eval';
    $event = 'onabort|onactivate|onafterprint|onafterupdate|onbeforeactivate|onbeforecopy|onbeforecut|onbeforedeactivate|onbeforeeditfocus|'.
    'onbeforepaste|onbeforeprint|onbeforeunload|onbeforeupdate|onblur|onbounce|oncellchange|onchange|onclick|oncontextmenu|oncontrolselect|'.
    'oncopy|oncut|ondataavailable|ondatasetchanged|ondatasetcomplete|ondblclick|ondeactivate|ondrag|ondragend|ondragenter|ondragleave|'.
    'ondragover|ondragstart|ondrop|onerror|onerrorupdate|onfilterchange|onfinish|onfocus|onfocusin|onfocusout|onhelp|onkeydown|onkeypress|'.
    'onkeyup|onlayoutcomplete|onload|onlosecapture|onmousedown|onmouseenter|onmouseleave|onmousemove|onmouseout|onmouseover|onmouseup|'.
    'onmousewheel|onmove|onmoveend|onmovestart|onpaste|onpropertychange|onreadystatechange|onreset|onresize|onresizeend|onresizestart|'.
    'onrowenter|onrowexit|onrowsdelete|onrowsinserted|onscroll|onselect|onselectionchange|onselectstart|onstart|onstop|onsubmit|onunload';

    if(!empty($allow)) {
        $allows = explode(',', $allow);
        $danger = str_replace($allow, '', $danger);
    }
    $danger = str_replace(',', '|', $danger);
    //替换所有危险标签
    $content = preg_replace("/<\s*({$danger})[^>]*>[^<]*(<\s*\/\s*\\1\s*>)?/is", '', $content);
    //替换所有危险的JS事件
    $content = preg_replace("/<([^>]*)({$event})\s*\=([^>]*)>/is", "<\\1 \\3>", $content);
    return $content;
}

/**
 * 获取加密后的安全码
 * @param $params
 * @return string
 */
function getEncryptSafecode($params, $token_key)
{
    return md5(md5($params['username']) . md5(substr($params['password'], 0, 6)) . $token_key);
}